USE QUANLYNHANVIEN
-- tao table DEAN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='DEAN')
BEGIN
DROP TABLE DEAN
END
GO
	CREATE TABLE DEAN
(	TENDA			NVARCHAR(30),
	MADA			INT IDENTITY(1,1),
	DDIEM_DA		NVARCHAR(30),
	PHONG			INT
		PRIMARY KEY (MADA)			)

--tao table DIADIEM_PHG
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='DIADIEM_PHG')
BEGIN
DROP TABLE DIADIEM_PHG
END
GO
	CREATE TABLE DIADIEM_PHG
(	MAPHG			INT,
	DIADIEM			NVARCHAR(20)
		PRIMARY KEY (MAPHG,DIADIEM)			)
	
--tao table NHANVIEN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='NHANVIEN')
BEGIN
DROP TABLE NHANVIEN
END
GO
	CREATE TABLE NHANVIEN
(	HONV			NVARCHAR(10),
	TENLOT			NVARCHAR(20),
	TENNV			NVARCHAR(10),
	MANV			NVARCHAR(10),
	NGSINH			DATETIME,
	DCHI			NVARCHAR(100),
	PHAI			NVARCHAR(5),
	LUONG			NUMERIC,
	MA_NQL			NVARCHAR(10),
	PHG				INT
		PRIMARY KEY (MANV)			)		

-- tao table PHONGBAN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='PHONGBAN')
BEGIN
DROP TABLE PHONGBAN
END
GO
	CREATE TABLE PHONGBAN
(	TENPHG			NVARCHAR(30),
	MAPHG			INT IDENTITY(1,1),
	TRPHG			NVARCHAR(10),
	NG_NHANCHUC		DATETIME
		PRIMARY KEY	(MAPHG)			)

-- tao table THANNHAN
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='THANNHAN')
BEGIN
DROP TABLE THANNHAN
END
GO
	CREATE TABLE THANNHAN
(	MA_NVIEN			NVARCHAR(10),
	TENTN				NVARCHAR(30),
	PHAI				NVARCHAR(5),
	NGSINH				DATETIME,
	QUANHE				NVARCHAR(20)
		PRIMARY KEY (MA_NVIEN,TENTN)			)

-- tao table PHANCONG
IF EXISTS ( SELECT * FROM DBO.SYSOBJECTS WHERE NAME ='PHANCONG')
BEGIN
DROP TABLE PHANCONG
END
GO
	CREATE TABLE	PHANCONG
(	MA_NVIEN			NVARCHAR(10),
	SODA				INT,
	THOIGIAN			NVARCHAR(10)
		PRIMARY KEY	(MA_NVIEN,SODA)			)
	
SELECT P.TENPHG,P.MAPHG,P.NG_NHANCHUC,N.HONV+' '+N.TENLOT+' '+N.TENNV 'TRPHG'
				FROM PHONGBAN P
				INNER JOIN NHANVIEN N ON N.MANV = P.TRPHG

UPDATE PHONGBAN SET TENPHG='Phong quan li',NG_NHANCHUC='03/23/2002' WHERE MAPHG=1
